<?php

namespace App\Admin\Controllers;

use App\Http\Controllers\Controller;
use Encore\Admin\Layout\Column;
use Encore\Admin\Layout\Content;
use Encore\Admin\Layout\Row;
use Encore\Admin\Widgets\Box;
use Illuminate\Support\Facades\DB;
use App\Models\School;
use App\Models\Student;

class HomeController extends Controller
{
    public function index(Content $content)
    {
        $school = School::where('id',1)->first();
//        $schoo_all = $school->toarray();//转化成数组
//        dd($schoo_all);
        $schoo_name = $school->name;
        $schoo_address = $school->address;
        $schoo_intro = $school->intro;

        $content->header('综合素质评价系统');
        $content->description('高职院校综合素质评价系统');
        $content->body(new Box('学校信息', view('admin.schoolDetails', ['school_name' => "$schoo_name", 'schoo_address' => "$schoo_address", 'schoo_intro' => "$schoo_intro"])));
        $content->row(function (Row $row) {
            $row->column(4, function (Column $column) {
                $majorsNameStr = '';
                $majorNumbers = '';

                $major_ids =    Student::select([DB::raw('count(1) as number'),'major_id'])->where('id', '>', '0')->where('graduation', 0)->groupBy('major_id')->orderBy('number', 'desc')->get();

                $count = count($major_ids);
                for ($i = 0; $i < $count; $i++) {
                    $major = DB::table('major')->where('id','=',$major_ids[$i]->major_id)->first();
                    $majorsNameStr .= '"' . mb_substr($major->title,0,2) . '"' . ',';
                    $majorNumbers .= $major_ids[$i]->number . ',';
                }
                $majorsNameStr = rtrim($majorsNameStr, ",");
                $majorNumbers = rtrim($majorNumbers, ",");

//                dd($majorsNameStr, $majorNumbers);
                $column->append( new Box('专业人数统计图', view('admin.majorChart', ['majorsNameStr' => $majorsNameStr, 'majorNumbers' => $majorNumbers])));
            });
            $row->column(4, function (Column $column) {
                $teacherManArr = ["50后" => 0,"60后" => 0,"70后" => 0,"80后" => 0,"90后" => 0];
                $tacherManStr = '';
                $teacherWomanArr= ["50后" => 0,"60后" => 0,"70后" => 0,"80后" => 0,"90后" => 0];
                $teacherWomanStr = '';
                $manSql ="select birthday, count(*) as num from (
SELECT 
case 
when birthday >= '1950' and birthday <= '1959' then '50后'
when birthday >= '1960' and birthday <= '1969' then '60后'
when birthday >= '1970' and birthday <= '1979' then '70后'
when birthday >= '1980' and birthday <= '1989' then '80后'
when birthday >= '1990' and birthday <= '1999' then '90后'
end 
as birthday from teacher WHERE on_duty = 0 AND sex = 0) a
WHERE 1=1
group by birthday";
                $womanSql ="select birthday, count(*) as num from (
SELECT 
case 
when birthday >= '1950' and birthday <= '1959' then '50后'
when birthday >= '1960' and birthday <= '1969' then '60后'
when birthday >= '1970' and birthday <= '1979' then '70后'
when birthday >= '1980' and birthday <= '1989' then '80后'
when birthday >= '1990' and birthday <= '1999' then '90后'
end 
as birthday from teacher WHERE on_duty = 0 AND sex = 1) a
WHERE 1=1
group by birthday";
                $teacherMans = DB::select($manSql);
                $teacherWomans = DB::select($womanSql);

                foreach ($teacherMans as $teacherMan) {
                    $teacherManArr[$teacherMan->birthday] = $teacherMan->num;
                }

                foreach($teacherManArr as $teacherNum) {
                    $tacherManStr .= $teacherNum . ',';
                }

                foreach ($teacherWomans as $teacherWoman) {
                    $teacherWomanArr[$teacherWoman->birthday] = $teacherWoman->num;
                }

                foreach($teacherWomanArr as $teacherNum) {
                    $teacherWomanStr .= $teacherNum . ',';
                }

                $teacherWomanStr = rtrim($teacherWomanStr, ",");

               $column->append( new Box('教师年龄分布', view('admin.teacherChart',['teacherNanArr' => $tacherManStr, 'teacherWomanArr' => $teacherWomanStr])));
            });
            $row->column(4, function (Column $column) {
                $studentBoy = Student::where('id','>',0)->where('graduation',0)->where('sex',0)->count();
                $studentGirl = Student::where('id','>',0)->where('graduation',0)->where('sex',1)->count();
                $studentElse = Student::where('id','>',0)->where('graduation',0)->where('sex','<>',0)->where('sex','<>',1)->count();
                $column->append( new Box('学生男女比例', view('admin.studentChart',['studentBoy' => $studentBoy, 'studentGirl' => $studentGirl, 'studnetElse' => $studentElse])));
            });
        });
        $content->row(function (Row $row) {
            $row->column(4, function (Column $column) {
//                $column->append( new Box('图表3', view('admin.schoolChart')));
            });
            $row->column(4, function (Column $column) {
//                $column->append( new Box('图表3', view('admin.schoolChart')));
            });
            $row->column(4, function (Column $column) {
//                $column->append( new Box('图表3', view('admin.schoolChart')));
            });
        });

        return $content;
    }

    public function tests(Content $content){
        return view('admin.schoolTitle');
    }
}
